﻿using System;
using System.Collections.Generic;
using System.Text;
using Model = SQLServerHealthCheck.Model;
using SQLServerHealthCheck.Model;
using System.Linq;
using System.Data.SqlClient;
using SMO = Microsoft.SqlServer.Management.Smo;
using WMI = Microsoft.SqlServer.Management.Smo.Wmi;
using System.Windows.Forms;
using System.Collections;
using System.IO;
using SQLServerHealthCheck.SQLServer.IssueTypes;
using System.Data.SqlTypes;
using AG.Utilities;

namespace SQLServerHealthCheck.SQLServer
{

    public class CollectDatabasesData
    {
        List<Exception> errors;
        HealthCheckDataContext dc;
        SQLServerInstance sqlServerInstance;

        public List<Exception> Collect(SQLServerInstance SqlServerInstance)
        {

            try
            {
                errors = new List<Exception>();
                sqlServerInstance = SqlServerInstance;
                dc = new HealthCheckDataContext(System.Configuration.ConfigurationManager.AppSettings["ConnectionStringSSHCDatabase"].ToString());

                
                FillDatabasesAndFiles();
                foreach (Database db in dc.Databases)
                {
                    if ((db.DatabaseName != "master") && (db.DatabaseName != "model") && (db.DatabaseName != "SQLServerOptimizer") && (db.DatabaseName != "tempdb") && (db.DatabaseName != "msdb") && (db.Service.InstanceName == sqlServerInstance.InstanceName))
                    CollectStoredProcedures(db);
                }
            }
            catch (Exception ex1)
            {
                Logger.Error(ex1);
            }

            return errors;
        }

        public void FillDatabasesAndFiles()
        {

            try
            {


                //InstallationCheck.WriteLoginFile("SQLServerHealthCheck_DatabaseCheckLog.txt", "FillDatabasesAndFiles: ", " FillDatabasesAndFiles method in DatabaseCheck Started for " + service.InstanceName.ToString());

                //Check the connection to SQL Server Instance and gets the details.

                String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                String serverName = parts[0];


                Server server = dc.Servers.FirstOrDefault(s => s.Name.ToLower() == serverName);

                Logger.Debug("FillDatabasesAndFiles, ServerName=" + serverName + ", Instance=" + sqlServerInstance.InstanceName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName.ToLower() == sqlServerInstance.InstanceName.ToLower());
                    if (service != null)
                    {

                        if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                        {
                            using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                            {
                                cn.Open();
                                String sql = GetSQLOverviewDatabasesAndFiles();

                                SqlCommand cmd = new SqlCommand(sql, cn );
                                SqlDataReader rd = cmd.ExecuteReader();

                                while (rd.Read())
                                {

                                   Logger.Debug("FillDatabasesAndFiles, Instance=" + sqlServerInstance.InstanceName + ", DatabaseName=" + rd[1].ToString());

                                    Database db = null;

                                    if (service.Databases.Count(d => d.DatabaseName == rd[1].ToString()) == 0)
                                    {
                                        db = new Database();

                                        db.DatabaseName = rd[1].ToString();
                                        db.Collation = rd.IsDBNull(2) ? "" : rd[2].ToString();
                                        db.Owner = rd[3].ToString();
                                        db.RecoveryModel = rd[4].ToString();
                                        db.State = rd[5].ToString();
                                        db.CompatibilityLevel = rd[6].ToString();

                                        SqlBoolean sqlBool = rd.GetBoolean(7);

                                        if (!sqlBool.IsNull)
                                        {
                                            db.IsAutoShrink = sqlBool.Value;
                                        }

                                        db.PageVerify = rd.IsDBNull(8) ? (int)0 : rd.GetInt32(8);

                                        SqlBoolean autoUpdatesqlBool = rd.GetBoolean(9);

                                        if (!autoUpdatesqlBool.IsNull)
                                        {
                                            db.AutoUpdate = autoUpdatesqlBool.Value;
                                        }


                                        db.DatabaseVersion = rd[10].ToString();
                                        db.CreateDate = Convert.ToDateTime(rd[11].ToString());
                                        db.CPUPercent = rd.IsDBNull(12) ? (Decimal?)null : rd.GetDecimal(12);
                                        if (!rd.IsDBNull(32))
                                        {
                                            Database database = service.Databases.FirstOrDefault(d => d.DatabaseName == rd[32].ToString());
                                            if (database != null)
                                            {
                                                db.SourceDatabaseId = database.DatabaseID;
                                            }
                                        }
                                        db.LatestBackUpPath = rd.IsDBNull(33) ? null : rd.GetString(33);
                                        db.LatestFullBackup = rd.IsDBNull(13) ? (DateTime?)null : rd.GetDateTime(13);
                                        db.LatestDifferentialBackup = rd.IsDBNull(14) ? (DateTime?)null : rd.GetDateTime(14);
                                        db.LatestLogBackup = rd.IsDBNull(15) ? (DateTime?)null : rd.GetDateTime(15);
                                        try { db.LatestFullBackupSize = Convert.ToInt32(rd["LatestFullBackupSize"]); }
                                        catch { }
                                        try { db.LatestFullBackupStart = Convert.ToDateTime(rd["LatestFullBackupStart"]); }
                                        catch { }
                                        service.Databases.Add(db);

                                    }
                                    else
                                    {
                                        db = service.Databases.First(d => d.DatabaseName == rd[1].ToString());
                                    }

                                    int offset = 18;
                                    
                                    if (!rd.IsDBNull(offset))
                                    {
                                        DatabaseFile dbfile = new DatabaseFile();

                                        dbfile.File = rd[offset].ToString();
                                        dbfile.FileGroup = rd[offset + 2].ToString();
                                        dbfile.Drive = rd[offset + 3].ToString();
                                        dbfile.SizeInMb = rd.GetDecimal(offset + 4);
                                        dbfile.FreeSpaceInMb = rd.IsDBNull(offset + 5) ? (Decimal?)null :rd.GetDecimal(offset + 5);
                                        if (dbfile.FileGroup == "LOG")
                                            dbfile.nrVLF = Convert.ToInt32(rd["vlfcount"]);
                                        SqlBoolean sqlBool = rd.GetBoolean(offset + 6);

                                        if (!sqlBool.IsNull)
                                        {
                                            dbfile.AutoGrowth = sqlBool.Value;
                                        }


                                        dbfile.MaxSize = rd.GetInt32(offset + 7);
                                        dbfile.GrowthPercent = rd.IsDBNull(offset + 8) ? (int?)null : rd.GetInt32(offset + 8);
                                        dbfile.GrowthMegaBytes = rd.IsDBNull(offset + 9) ? (int?)null : rd.GetInt32(offset + 9);

                                        dbfile.IOStallReadMs = rd.IsDBNull(offset + 10) ? (Int64?)null : rd.GetInt64(offset + 10);
                                        dbfile.NumOfReads = rd.IsDBNull(offset + 11) ? (Int64?)null : rd.GetInt64(offset + 11);
                                        dbfile.IOStallWriteMs = rd.IsDBNull(offset + 12) ? (Int64?)null : rd.GetInt64(offset + 12);
                                        dbfile.NumOfWrites = rd.IsDBNull(offset + 13) ? (Int64?)null : rd.GetInt64(offset + 13);


                                        db.DatabaseFiles.Add(dbfile);
                                    }
                                    dc.SubmitChanges();
                                }

                                rd.Close();


                            }

                        //    dc.SubmitChanges();

                        }



                    }
                }
            }
            catch (Exception ex)
            {

                errors.Add(ex);
                Logger.Error(ex);
            }


        }

        private String GetSQLOverviewDatabasesAndFiles()
        {
            String sql = @" SET TRANSACTION ISOLATION LEVEL Read uncommitted;
                                    BEGIN TRANSACTION;
	   DECLARE @versionString            VARCHAR(20),
        @serverVersion            DECIMAL(10,5),
        @sqlServer2012Version    DECIMAL(10,5)
 
SET        @versionString    = CAST(SERVERPROPERTY('productversion') AS VARCHAR(20))
SET        @serverVersion = CAST(LEFT(@versionString,CHARINDEX('.', @versionString)) AS DECIMAL(10,5))
SET        @sqlServer2012Version = 11.0 -- SQL Server 2012

IF(@serverVersion >= @sqlServer2012Version)
    BEGIN
        -- Use the new version of the table  
        DECLARE @logInfoResult2012 TABLE
            (
            [RecoveryUnitId]    INT NULL,
            [FileId]            INT NULL,
            [FileSize]            BIGINT NULL,
            [StartOffset]        BIGINT NULL,
            [FSeqNo]            INT NULL,
            [Status]            INT NULL,
            [Parity]            TINYINT NULL,
            [CreateLSN]            NUMERIC(25, 0) NULL
            )
    END  
ELSE  
    BEGIN
        -- Use the old version of the table
        DECLARE @logInfoResult2008 TABLE
            (
            [FileId]            INT NULL,
            [FileSize]            BIGINT NULL,
            [StartOffset]        BIGINT NULL,
            [FSeqNo]            INT NULL,
            [Status]            INT NULL,
            [Parity]            TINYINT NULL,
            [CreateLSN]            NUMERIC(25, 0) NULL
            )
 
    END

declare @query1 varchar(500) 
declare @dbname sysname 
declare @vlfs int 
 
--table variable used to 'loop' over databases 
 IF(@serverVersion >= @sqlServer2012Version)
declare @databases table (dbname sysname) 
insert into @databases 
--only choose online databases 
select name from sys.databases 
 
--table variable to hold results 
declare @vlfcounts table 
    (dbname sysname, 
    vlfcount int) 
 
--table varioable to capture DBCC loginfo output 


while exists(select top 1 dbname from @databases) 
begin 
 
    set @dbname = (select top 1 dbname from @databases) 
	set @query1 = 'dbcc loginfo (' + '''' + @dbname + ''') ' 

	 IF(@serverVersion >= @sqlServer2012Version)
	 begin
	     insert into @logInfoResult2012 	exec (@query1) 
	set @vlfs = @@rowcount 
 end
 else 
 begin 
     insert into @logInfoResult2008 	exec (@query1) 
	set @vlfs = @@rowcount 
 end
    insert @vlfcounts 
    values(@dbname, @vlfs) 
 
    delete from @databases where dbname = @dbname 
 
end 

--output the full list 
--select dbname, vlfcount 
--from @vlfcounts 

--order by dbname

Create table #Database_BackupDetails(
	  
		                                DatabaseName varchar(200),
		                                LatestFullBackup [datetime] NULL,
		                                LatestDifferentialBackup [datetime] NULL,
		                                LatestLogBackup [datetime] NULL,
		                                LatestFullBackupStart [datetime] NULL,
		                                LatestFullBackupSize int
	                                )


CREATE TABLE #tmpbk
(name varchar(2000),
[type] varchar(12),
enddate datetime,
backuppath varchar(260)
)
	                               Create table #Database(
	                                    [DatabaseID] int,
		                                DatabaseName varchar(200),
		                                Collation varchar(500) NULL,
		                                Create_date [datetime] NULL,
		                                [Owner] varchar(200) NULL,
		                                RecoveryModel varchar(20) NULL,
		                                State varchar(100) NULL,
		                                [CompatibilityLevel] [varchar](50) NULL,
                                        IsAutoShrink bit NULL,
                                        [Page_Verify_Option] int,
                                        [is_auto_update_stats_on] bit NULL,
										[source_database_id] int NULL
										)
	
	                                Create table #Database_FileDetails(
		                                DatabaseName varchar(200),
		                                [File] varchar(150) NULL, 
										[PhysicalName] varchar(4000) NULL,
		                                FileGroup varchar(150) NULL, 
		                                Drive varchar(50) NULL,
		                                SizeInMb decimal NULL,
										FreeSpaceInMB decimal NULL,
                                        AutoGrowth bit NULL,
                                        MaxSize int NULL,
                                        GrowthPercent int NULL,
                                        GrowthMegaBytes int NULL
                                        
	                                )
                             

                                declare @query varchar(8000)
                                Set @query=''
                                DECLARE 
	                                @databaseID int,
	                                @database varchar(150),
	                                @collation varchar(500),
	                                @owner varchar(200),
	                                @recoveryModel varchar(20),
	                                @state varchar(100),
	                                @create_date [datetime] ,
	                                @LatestFullBackup [datetime] ,
	                                @LatestDifferentialBackup [datetime] ,
	                                @LatestLogBackup [datetime] ,
	                                @LatestFullBackupStart [datetime],
	                                @LatestFullBackupDuration int,
	                                @CompatibilityLevel [varchar](50),
                                    @IsAutoShrink [BIT],
                                    @Page_Verify_Option int,
                                    @is_auto_update_stats_on [BIT],
									@source_database_id int
								
	


									SELECT d.name , 
										   d.database_id , 
										   d.collation_name,
										   d.Create_date, 
										   ISNULL(p.name,'') as [Owner], 
										   d.recovery_model_desc, 
										   d.state_desc, 
										   d.[compatibility_level],
										   d.is_auto_shrink_on,
										   d.Page_Verify_Option,
										   d.is_auto_update_stats_on,
										   d.source_database_id
										INTO #DatabaseCursor
	                                FROM 
                                        sys.databases d 
                                            LEFT OUTER JOIN 
                                        sys.server_principals p  ON d.owner_sid=p.sid 
                                        
		
                                DECLARE databases_cursor CURSOR FOR
	                                 SELECT d.name , 
										   d.database_id , 
										   d.collation_name,
										   d.Create_date, 
										   d.Owner,
										   d.recovery_model_desc, 
										   d.state_desc, 
										   d.[compatibility_level],
										   d.is_auto_shrink_on,
										   d.Page_Verify_Option,
										   d.is_auto_update_stats_on,
										   d.source_database_id
										
	                                FROM 
                                        #DatabaseCursor d 
                                        

                                OPEN databases_cursor
								


                                FETCH NEXT FROM databases_cursor INTO @database, @databaseid, @collation,@create_date ,@owner, @recoveryModel, @state, @CompatibilityLevel,@IsAutoShrink,@Page_Verify_Option,@is_auto_update_stats_on,@source_database_id
                         
							 
							    WHILE @@FETCH_STATUS = 0
							     BEGIN

								 BEGIN TRY
                                Set @query='
								USE [' + @database + ']
                                INSERT INTO #Database
                                (	
	                                [DatabaseID],
	                                DatabaseName,
	                                Collation ,	
	                                Create_date ,
	                                [Owner],
	                                RecoveryModel,
	                                State,
	                                [CompatibilityLevel],
                                    IsAutoShrink,
                                    Page_Verify_Option,
                                    is_auto_update_stats_on,
									source_database_id
								
                                )
                                VALUES
                                (
	                                ' + cast(@databaseid as varchar(100)) + ',
	                                '''+@database + ''',
	                                '''+ ISNULL( @collation,'')  + ''',
	                                '''+ cast(@create_date as varchar(100)) + ''',
	                                '''+@owner + ''',
	                                '''+@recoveryModel+ ''',
	                                '''+@state + ''',
	                                '''+@CompatibilityLevel + ''',
                                    '''+ CAST(@IsAutoShrink AS CHAR(1)) + ''',
                                    '''+ CAST(@Page_Verify_Option as varchar(2))+ ''',
                                    '''+ CAST(@is_auto_update_stats_on  AS CHAR(1)) + ''',									
									'''+ cast(isnull(@source_database_id,-1) as varchar(100)) + '''
									
									
                                )

	                                insert into #Database_BackupDetails( DatabaseName, LatestFullBackupStart, LatestFullBackupSize) 
	
	
	                                 SELECT [Database] , [BACKUP_START] as LatestFullBackupStart, [BACKUP_SIZE] as LatestFullBackupSize
				                                FROM
				                                (
					                                SELECT 
						                                DATABASE_NAME [Database],
						                                BackupType=
							                                CASE 
								                                WHEN [TYPE]=''D'' THEN ''Full'' 
								                                WHEN [TYPE]=''I'' THEN ''Differential'' 
								                                WHEN [TYPE]=''L'' THEN ''Log''
								                                WHEN [TYPE]=''F'' THEN ''File / Filegroup''
								                                WHEN [TYPE]=''G'' THEN ''Differential file''
								                                WHEN [TYPE]=''P'' THEN ''Partial''
								                                WHEN [TYPE]=''Q'' THEN ''Differential partial''
							                                END,
							                            cast(MAX(BACKUP_SIZE) as int) BACKUP_SIZE,
						                                cast(MAX(BACKUP_START) as varchar(100)) BACKUP_START,
						                                cast(MAX(BACKUP_END) as varchar(100)) BACKUP_END
						                                
					                                FROM 
						                                (
							                                SELECT 
								                                ROW_NUMBER() OVER (ORDER BY DATABASE_NAME,[TYPE],BACKUP_FINISH_DATE) ID,
								                                A.BACKUP_FINISH_DATE AS BACKUP_END,
								                                A.BACKUP_START_DATE AS BACKUP_START ,
								                                A.BACKUP_SIZE AS BACKUP_SIZE,
								                                B.PHYSICAL_DEVICE_NAME,
								                                A.MEDIA_SET_ID,
								                                A.DATABASE_NAME,
								                                [TYPE]
							                                FROM 
								                                MSDB.DBO.BACKUPSET A JOIN 
								                                MSDB.DBO.BACKUPMEDIAFAMILY B ON(A.MEDIA_SET_ID=B.MEDIA_SET_ID)
								                                where A.database_name=''' + @database +
						                                ''') AS SUB
					                                GROUP BY [TYPE],DATABASE_NAME
					
				                                ) AS S 
				                              

                                BEGIN TRY

	                                IF (not '''+ @state + ''' = ''OFFLINE'')
	                                BEGIN

		                                insert into #Database_FileDetails
		                                (
			                                DatabaseName,
			                                Drive,
			                                Filegroup,
			                                [File],
											[PhysicalName],
			                                SizeinMB,
											FreeSpaceInMB,
                                            AutoGrowth,
                                            MaxSize,
                                            GrowthPercent,
                                            GrowthMegaBytes
                                             
		                                )
		                                SELECT	
			                                '''+@database + ''',
			                                Left(df.physical_name,3) AS Drive,
			                                ISNULL(f.name,''LOG'') AS FileGroup,
			                                RIGHT(df.physical_name, CHARINDEX(''\'', REVERSE(df.physical_name))-1) AS [File], 
											df.physical_name,
			                                df.size*8/1024  AS SizeInMb,
											convert(decimal(12,2),round((df.size-fileproperty(df.name,''SpaceUsed''))/128.000,2)) as FreeSpaceMB ,
                                            CASE
                                            WHEN df.growth = 0 THEN 0
                                            ELSE 1
                                            END AS AutoGrowth,
                                            df.max_size AS MaxSize,
                                            CASE 
                                              WHEN df.is_percent_growth =1 THEN df.growth  
                                              ELSE NULL
                                            END AS GrowthPercent,
                                            CASE 
                                              WHEN df.is_percent_growth =1 THEN NULL 
                                              ELSE CAST( CAST(df.growth AS Decimal(6,2)) * 8.0 /1024.0 AS integer)
                                            END AS GrowthMegaBytes
		                                FROM         
			                                [' + @database + '].sys.database_files df 
												LEFT OUTER JOIN
			                                [' + @database + '].sys.filegroups f ON df.data_space_id = f.data_space_id  
	                                END	
                                END TRY
                                BEGIN CATCH
								
                                END CATCH
                                '
								

								

                                exec( @query)

								END TRY
								BEGIN CATCH
									INSERT INTO #Database
									(	
	                                [DatabaseID],
	                                DatabaseName,
	                                Collation ,	
	                                Create_date ,
	                                [Owner],
	                                RecoveryModel,
	                                State,
	                                [CompatibilityLevel],
                                    IsAutoShrink,
                                    Page_Verify_Option,
                                    is_auto_update_stats_on,
									source_database_id
								
									)
									VALUES
									(
									@databaseid, 
									@database, 
									@collation,
									@create_date ,
									@owner, 
									@recoveryModel,
									@state,
									@CompatibilityLevel,
									@IsAutoShrink,
									@Page_Verify_Option,
									@is_auto_update_stats_on,
									@source_database_id 


									)
							
								END CATCH
							
                                FETCH NEXT FROM databases_cursor INTO @database, @databaseid, @collation,@create_date ,@owner, @recoveryModel, @state, @CompatibilityLevel,@IsAutoShrink,@Page_Verify_Option,@is_auto_update_stats_on,@source_database_id 
							
                                 END
 
                                CLOSE databases_cursor
                                DEALLOCATE databases_cursor;
                                insert into #tmpbk (name ,[type] ,enddate,backuppath ) 
								Select database_name, 
								CASE
								WHEN [TYPE]='D' THEN 'Full'
								WHEN [TYPE]='I' THEN 'Differential' 
								WHEN [TYPE]='L'THEN 'Log'
								End
								,backup_finish_date ,
								physical_device_name
								from  MSDB.DBO.BACKUPSET b
								JOIN msdb.dbo.backupmediafamily m ON b.media_set_id = m.media_set_id;

                                
                                

								WITH DB_CPU_Stats
								AS
								(
									SELECT DatabaseID, DB_Name(DatabaseID) AS [DatabaseName], SUM(total_worker_time) AS [CPU_Time_Ms]
									FROM sys.dm_exec_query_stats AS qs
									CROSS APPLY (SELECT CONVERT(int, value) AS [DatabaseID] 
												FROM sys.dm_exec_plan_attributes(qs.plan_handle)
												WHERE attribute = N'dbid') AS F_DB
									GROUP BY DatabaseID
								)
                                SELECT 
	                                D.DatabaseID as DatabaseID,
	                                D.DatabaseName as DatabaseName,
	                                D.Collation as Collation,
	                                D.[Owner] as [Owner],
	                                D.RecoveryModel as RecoveryModel,
	                                D.[State] as [State],
	                                D.CompatibilityLevel as CompatibilityLevel,
                                    D.IsAutoShrink as [AutoShrink],
                                    D.Page_Verify_Option as [Page_Verify_Option],
                                    D.is_auto_update_stats_on  as [Auto_update_stats], 
									
									DatabaseProperty (D.DatabaseName, 'version') as DatabaseVersion,
									D.Create_date as CreateDate,
									CPU.CPUPercent,

	                              (select top 1 enddate from #tmpbk where type='Full'  and name = D.DatabaseName order by enddate desc) as LatestFullBackup,
	                               (select top 1 enddate from #tmpbk where type='Differential'  and name = D.DatabaseName order by enddate desc) as LatestDifferentialBackup,
	                               (select top 1 enddate from #tmpbk where type='Log'  and name = D.DatabaseName order by enddate desc) as LatestLogBackup, 
								   
	                                DB.LatestFullBackupStart as LatestFullBackupStart,
	                                DB.LatestFullBackupSize as LatestFullBackupSize,
	                                [DF].[File] as [File],
									DF.PhysicalName,
	                                DF.FileGroup as FileGroup,
	                                DF.Drive as Drive,
	                                DF.SizeInMb as SizeInMb,
									DF.FreeSpaceInMB AS FreeSpaceInMB, 
                                    [DF].[AutoGrowth] as [AutoGrowth],
                                    [DF].[MaxSize] as Max_Growth_Size,
                                    [DF].[GrowthPercent] as Growth_Percentage, 
                                    [DF].[GrowthMegaBytes] as Growth_MegaBytes, 
									IO.io_stall_read_ms, 
									IO.num_of_reads,
									IO.io_stall_write_ms, 
									IO.num_of_writes,
									temp.vlfcount,
									
									
                                    (select d2.DatabaseName from #Database d2 where D.source_database_id = d2.DatabaseID) as source_database_name,
									 (select top 1 backuppath from #tmpbk where name = D.DatabaseName) as backuppath
                                FROM 
	                                #Database AS D 
		                                 left outer join 
	                                #Database_FileDetails as DF on D.DatabaseName = DF.DatabaseName  
		                                left outer join 
	                                #Database_BackupDetails as DB on D.DatabaseName=DB.DatabaseName 
										 left outer join
									(

										SELECT 
											fs.database_id, 
											mf.physical_name, 
											io_stall_read_ms, 
											num_of_reads,
											io_stall_write_ms, 
											num_of_writes
										FROM 
											sys.dm_io_virtual_file_stats(null,null) AS fs
												INNER JOIN 
											sys.master_files AS mf ON fs.database_id = mf.database_id AND fs.[file_id] = mf.[file_id]
									) AS IO ON DF.PhysicalName = IO.physical_name
										left outer join
									(
										SELECT 
											   DatabaseID,DatabaseName,        
											   CAST([CPU_Time_Ms] * 1.0 / SUM([CPU_Time_Ms]) OVER() * 100.0 AS DECIMAL(5, 2)) AS [CPUPercent]
										FROM DB_CPU_Stats

									) AS CPU ON D.DatabaseID = CPU.DatabaseID
									left outer join
									(select vlfcount, dbname from @vlfcounts) as temp on d.DatabaseName = dbname
										order by source_database_name 
                       
                                drop table #Database
                                drop table #Database_FileDetails
                                drop table #Database_BackupDetails
								DROP TABLE #DatabaseCursor
							    drop table #tmpbk
COMMIT TRANSACTION

";
            return sql;
        }

     
        private void CollectSQLServerServiceInfo(Service service)
        {

            try
            {
                //WriteLoginFile("SQLServerHealthCheck_InstallationCheckLog.txt", "FillSQLServerServiceInfo: ", " FillSQLServerServiceInfo method in Installation Check Started....");


                using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                {



                    cn.Open();

                    //enable Ole Automation Procedures using sp_configure
                    string sqlOLE = "sp_configure 'show advanced options',1 ;reconfigure with override;";
                    SqlCommand OLEcmd = new SqlCommand(sqlOLE, cn);
                    OLEcmd.ExecuteNonQuery();

                    string sqlOLE1 = "sp_configure 'Ole Automation Procedures',1 ;reconfigure with override;";
                    SqlCommand OLEcmd1 = new SqlCommand(sqlOLE1, cn);
                    OLEcmd1.ExecuteNonQuery();

                    //enabling  Ole Automation Procedures using sp_configure ends




                    //Get product Version,Edition etc
                    String sql = @" SET TRANSACTION ISOLATION LEVEL Read uncommitted;
                                    BEGIN TRANSACTION;
                                    SELECT 
                                    cast(SERVERPROPERTY('Edition' ) as varchar(100)) As Edition,
                                    REPLACE( cast(SERVERPROPERTY('ProductVersion' )as varchar(13)), '.0.','.00.') AS ProductVersion,
                                    SERVERPROPERTY ('productlevel') as Productlevel;
                                    COMMIT TRANSACTION"
                        ;
                    SqlCommand cmd = new SqlCommand(sql, cn);
                    using (SqlDataReader rd = cmd.ExecuteReader())
                    {
                        while (rd.Read())
                        {

                            service.ProductVersion = rd["ProductVersion"].ToString();
                            service.Edition = rd["Edition"].ToString();
                            service.Description = rd["Productlevel"].ToString();
                        }
                    }

                }

                dc.SubmitChanges();

            }
            catch (Exception ex)
            {
                errors.Add(ex);
                Logger.Error(ex);
            }


        }
        private void CollectStoredProcedures(Database database)
        {

            try
            {

                String[] parts = sqlServerInstance.InstanceName.ToLower().Split('\\');
                String serverName = parts[0];


                Server server = dc.Servers.FirstOrDefault(s => s.Name.ToLower() == serverName);

                Logger.Debug("FillDatabasesAndFiles, ServerName=" + serverName + ", Instance=" + sqlServerInstance.InstanceName);

                if (server != null)
                {

                    Service service = server.Services.FirstOrDefault(s => s.InstanceName.ToLower() == sqlServerInstance.InstanceName.ToLower());
                    if (service != null)
                    {

                        if (CollectServerAndServiceData.IsSQLServerAccessible(sqlServerInstance.GetConnectionString("master")))
                        {
                            using (SqlConnection cn = new SqlConnection(sqlServerInstance.GetConnectionString("master")))
                            {
                                cn.Open();
                                String sql = @"SET TRANSACTION ISOLATION LEVEL Read uncommitted; BEGIN TRANSACTION;    
                                                USE [" + database.DatabaseName + @"] 
                                                IF OBJECT_ID('sys.schemas') IS NULL 
                                                    SELECT B.name + '.' + A.name, C.text 
                                                    FROM sysobjects A, sysusers B, syscomments C 
                                                    WHERE A.type = 'P' AND B.uid = OBJECTPROPERTY ( A.id , 'ownerid')   and A.id = C.id 
                                                    ORDER BY A.name 
                                                ELSE 
                                                    SELECT B.name + '.' + A.name, C.text 
                                                    FROM sysobjects A, sys.schemas B, syscomments C 
                                                    WHERE A.type = 'P' AND B.schema_id = A.uid  and A.id = C.id 
                                                    ORDER BY B.name + '.' + A.name ; 
                                            COMMIT TRANSACTION ";

                                SqlCommand cmd = new SqlCommand(sql, cn);
                                SqlDataReader rd = cmd.ExecuteReader();

                                StoredProcedure sp;

                                while (rd.Read())
                                {
                                     sp = new StoredProcedure();
                                    sp.Database = database;
                                    sp.Name = rd[0] == null ? null : rd[0].ToString();
                                    sp.Text = rd[1] == null ? null : rd[1].ToString();
                                    dc.StoredProcedures.InsertOnSubmit(sp);
                                    dc.SubmitChanges();

                                }
                               
                                rd.Close();
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                errors.Add(ex);
                Logger.Error(ex);
            }
        

        }
       




        }
}
